﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace REPORT.DAL
{
    class DbProvider
    {
         SqlConnection sqlCnn;
    SqlCommand sqlCmd;
	public DbProvider()
	{
        ClsConnection connect = new ClsConnection(@"(local)","XuatHoaDon",true);
        sqlCnn = new SqlConnection(connect.getConnectionString());
        //Khoi tao doi tuong Command dua vao doi tuong ket noi SqlConn
        sqlCmd = sqlCnn.CreateCommand();
	}
    public void Open()
    {
        if (sqlCnn.State != ConnectionState.Open)
            sqlCnn.Open();
    }

    //Phuong thuc giai phong doi tuong (tai nguyen)
    public void Dispose()
    {
        //Ngat ket noi
        if (sqlCnn.State != ConnectionState.Closed)
            sqlCnn.Close();
        //Giai phong doi tuong
        sqlCnn.Dispose();
        sqlCmd.Dispose();
    }
    public void AddParameter(SqlParameter para)
    {
        sqlCmd.Parameters.Add(para);
    }
    //Phuong thuc rut trich du lieu (SELECT)
    public DataTable ExecuteQuery(string strSQL)
    {
        DataTable tbObject = new DataTable();
        SqlDataAdapter sqlAdap = new SqlDataAdapter();
        try
        {
            //Mo ket noi
            this.Open();
            //Gan cac thuoc tinh cho doi tuong Command
            sqlCmd.CommandText = strSQL;
            sqlCmd.CommandType = CommandType.Text;
            sqlAdap.SelectCommand = sqlCmd;
            //Do du lieu
            sqlAdap.Fill(tbObject);
        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            this.Dispose();
        }
        return tbObject;
    }

    //Phuong thuc cap nhat du lieu (INSERT, DELETE, UPDATE, ...)
    public int ExcuteNoneQuery(string strSQL)
    {
        int row = 0;
        try
        {
            this.Open();
            sqlCmd.CommandText = strSQL;
            sqlCmd.CommandType = CommandType.Text;
            row = sqlCmd.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            this.Dispose();
        }
        return row;
    }
    public SqlDataReader ExcuteReader(string sql)
    {
        Open();
        SqlCommand sqlCmmd = new SqlCommand(sql,sqlCnn);
        SqlDataReader sqldtR = sqlCmmd.ExecuteReader();
        return sqldtR;
    }
    public DataSet DsExecuteQuery(string strSQL)
    {
        DataSet dsObject = new DataSet();
        SqlDataAdapter sqlAdap = new SqlDataAdapter();
        try
        {
            //Mo ket noi
            this.Open();
            //Gan cac thuoc tinh cho doi tuong Command
            sqlCmd.CommandText = strSQL;
            sqlCmd.CommandType = CommandType.Text;
            sqlAdap.SelectCommand = sqlCmd;
            //Do du lieu
            sqlAdap.Fill(dsObject);
        }
        catch (SqlException ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            this.Dispose();
        }
        return dsObject;
    }
    }
}
